SQL date select statement, part 6

The following SELECT statements would give you the equivalent date value:

YYYYMMDD

SELECT CONVERT(varchar(20),getdate(),112)


DD MMM YYY HH:MM:SS:MMM

SELECT CONVERT(varchar(30),getdate(),113)


HH:MM:SS:MMM

SELECT CONVERT(varchar(30),getdate(),114)


YYYY-MM-DD HH:MM:SS

SELECT CONVERT(varchar(30),getdate(),120)


SQL date select statement, part 5

The following SELECT statements would give you the equivalent date value:

DD MMM YYYY

SELECT CONVERT(varchar(20),getdate(),106)


MMM DD, YYYY

SELECT CONVERT(varchar(20),getdate(),107)


HH:MM:SS

SELECT CONVERT(varchar(20),getdate(),108)


MMM DD YYY H:MM:SS AM/PM

SELECT CONVERT(varchar(30),getdate(),109)


MM/DD/YYYY

SELECT CONVERT(varchar(20),getdate(),110)


YYYY/MM/DD

SELECT CONVERT(varchar(20),getdate(),111)


SQL date select statement, part 4

The following SELECT statements would give you the equivalent date value:

MM/DD/YYYY

SELECT CONVERT(varchar(20),getdate(),101)


MM-DD-YYYY

SELECT REPLACE(convert(varchar(20),getdate(),101),'/','-')


DD/MM/YYYY

SELECT CONVERT(varchar(20),getdate(),103)


DD.MM.YYYY

SELECT CONVERT(varchar(20),getdate(),104)


DD-MM-YYYY

SELECT CONVERT(varchar(20),getdate(),105)


SQL date select statement, part 3

The following SELECT statements would give you the equivalent date value:

Quarter of the year

SELECT DATENAME(QQ, getdate())


Hour of the day

SELECT DATEPART(HH, getdate())


Minute of the hour

SELECT DATEPART(MI, getdate())


Second of the hour

SELECT DATEPART(SS, getdate())


SQL date select statement, part 2

The following SELECT statements would give you the equivalent date value:

Day of the Month

SELECT DATEPART(DD, getdate())


Week of the year

SELECT DATEPART(WW, getdate())


Day of the week

SELECT DATEPART(DW, getdate())


Name of the day

SELECT DATENAME(DW, getdate())


SQL date select statement, part 1

The following SELECT statements would give you the equivalent date value:

Year

SELECT DATEPART(YY, getdate())


Days in a year

SELECT DATEPART(DY, getdate())


Month

SELECT DATEPART(MM, getdate())


Month name

SELECT DATENAME(MM, getdate())


date subtraction

The following code will compute for the number of months between the two dates.


Dim myMonths As Integer = DateDiff(DateInterval.Month, myDate, System.DateTime.Now)


Or, use TimeSpan.


Dim startDate As Date = "1/1/2007"
Dim myTimeSpan As TimeSpan
Dim myDays As Integer
myTimeSpan = Now.Subtract(startDate)
myDays = myTimeSpan.Days