Thứ Ba, ngày 08 tháng 7 năm 2014

MSsql (sql server) convert Datetime / Timestamp into Unix timestamp (bigint)

select CURRENT_TIMESTAMP;

select CONVERT(BIGINT, DATEDIFF(S, '19700101', '20380119')) + CONVERT(BIGINT, DATEDIFF(S, '20380119', CURRENT_TIMESTAMP)) ;

select DateAdd(SECOND, CONVERT(BIGINT, DATEDIFF(S, '19700101', '20380119')) + CONVERT(BIGINT, DATEDIFF(S, '20380119', CURRENT_TIMESTAMP)), '1970-01-01T00:00:00.000') AS TIME_MS;


convert from unix time to sql server datetime:
SELECT DATEADD(s, mycolumn / 1000, '19700101') from mytable
 
It would seem that we could use "ms" instead of the "s" parameter and avoid division by 1000, since the data are stored in milliseconds in mycolumn. However, that created an overflow condition. My only guess is that the dataadd function uses integer math internally instead of bigint.
Next up, how to convert the other direction:
SELECT cast(DATEDIFF(s, '19700101', cast('2012-10-10 14:05:00.000' as datetime)) as bigint) * 1000 from mytable
 
You'll want to replace '2012-10-10 14:05:00.000' with your own date/time string. Again we have this overflow problem if we try to get milliseconds directly in datediff, so we have to multiply by 1000.
This is all well and fine, but my data was not in the GMT timezone. So I additionally need to convert through to my timezone (CST).
SELECT cast(DATEDIFF(s, '19700101', dateadd(hh, 5, cast('2012-10-10 14:05:55.000' as datetime))) as bigint) * 1000 from mytable
 
As before, replace '2012-10-10 14:05:55.000' with your own date/time string.



SQL SERVER – Few Useful DateTime Functions to Find Specific Dates


Recently I have recieved email from 
----TodaySELECT GETDATE() 'Today'----YesterdaySELECT DATEADD(d,-1,GETDATE()) 'Yesterday'----First Day of Current WeekSELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)'First Day of Current Week'----Last Day of Current WeekSELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)'Last Day of Current Week'----First Day of Last WeekSELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0)'First Day of Last Week'----Last Day of Last WeekSELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6)'Last Day of Last Week'----First Day of Current MonthSELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)'First Day of Current Month'----Last Day of Current MonthSELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))'Last Day of Current Month'----First Day of Last MonthSELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))'First Day of Last Month'----Last Day of Last MonthSELECTDATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))'Last Day of Last Month'----First Day of Current YearSELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)'First Day of Current Year'----Last Day of Current YearSELECTDATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))'Last Day of Current Year'----First Day of Last YearSELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))'First Day of Last Year'----Last Day of Last YearSELECTDATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))'Last Day of Last Year'
ResultSet:
Today
———————–
2008-08-29 21:54:58.967
Yesterday
———————–
2008-08-28 21:54:58.967
First Day of Current Week
————————-
2008-08-25 00:00:00.000
Last Day of Current Week
————————
2008-08-31 00:00:00.000
First Day of Last Week
———————–
2008-08-18 00:00:00.000
Last Day of Last Week
———————–
2008-08-24 00:00:00.000
First Day of Current Month
————————–
2008-08-01 00:00:00.000
Last Day of Current Month
————————-
2008-08-31 23:59:59.997
First Day of Last Month
———————–
2008-07-01 00:00:00.000
Last Day of Last Month
———————–
2008-07-31 23:59:59.997
First Day of Current Year
————————-
2008-01-01 00:00:00.000
Last Day of Current Year
————————
2008-12-31 23:59:59.997
First Day of Last Year
———————–
2007-01-01 00:00:00.000
Last Day of Last Year
———————–
2007-12-31 23:59:59.997
Reference : Pinal Dave (http://blog.SQLAuthority.com)

Thứ Tư, ngày 09 tháng 4 năm 2014

SQL SERVER – Find Row Count in Table – Find Largest Table in Database.

SELECT sc.name +'.'+ ta.name TableName
 ,SUM(pa.rows) RowCnt
 FROM sys.tables ta
 INNER JOIN sys.partitions pa
 ON pa.OBJECT_ID = ta.OBJECT_ID
 INNER JOIN sys.schemas sc
 ON ta.schema_id = sc.schema_id
 WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
 GROUP BY sc.name,ta.name
 ORDER BY SUM(pa.rows) DESC
More:

to get all tables in a database:
select * from INFORMATION_SCHEMA.TABLES
to get all columns in a database:
select * from INFORMATION_SCHEMA.columns
to get all views in a db:
select * from INFORMATION_SCHEMA.TABLES where table_type = 'view'