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

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


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'
2008-08-29 21:54:58.967
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 (

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

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

SELECT +'.'+ TableName
 ,SUM(pa.rows) RowCnt
 FROM sys.tables ta
 INNER JOIN sys.partitions pa
 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)

to get all tables in a database:
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'