1. Home »
  2. Analytics AI »
  3. User guide »
  4. DWS »
  5. Create DWS project »
  6. Configuration »
  7. How to identify the source data date format ?

How to identify the source data date format ?

This document will explain how to give date format in Bizstats.AI > create project > configuration screen.

To identify the correct date format, Bizstats.AI gets the source date format in this text box. For different databases/ data warehouses uses different format expressions. use appropriate date format as per your data source connection. 

Step 1: Please find your connection to which database/ data warehouse ( Ex: Bigquery, Snowflake, Redshift) for this respective project

Step 2: Choose the date format example specific to your database/data warehouse.

Actual Source Date Data

 Date Format Expression
BigQuery Snowflake Redshift
19701130 %Y%m%d (or) %C%y%m%d YYYYMMDD YYYYMMDD
701130 %y%m%d Not Available YYMMDD
1970/11/30 %Y/%m/%d (or) %x YYYY/MM/DD YYYY/MM/DD
1970-11-30 %Y-%m-%d (or) %F YYYY-MM-DD YYYY-MM-DD
1970 Nov 30 %Y %b %d (or) %Y %h %d YYYY MON DD YYYY Mon DD
1970 November 30 %Y %B %d YYYY MMMM DD YYYY Month DD
11301970 %m%d%Y MMDDYYYY MMDDYYYY
11-30-1970 %m-%d-%Y MM-DD-YYYY MM-DD-YYYY
30.11.1970 %d.%m.%Y DD.MM.YYYY DD.MM.YYYY
Wednesday Nov 11 1970 %A %b %e %Y Not Available Day Mon DD YYYY
Wed Nov 11 1970 %a %b %e %Y DY MON DD YYYY Dy Mon DD YYYY

 

Bigquery date format

Format Representation
%Y 4 digit year number (for example: 1970 , 2000, 2019)
%y 2 digit year number ( for example: 70, 00, 19)
%B The full month name.
%b or %h The abbreviated month name
%m The month as a decimal number (01-12).
%D The date in the format %m/%d/%y.
%d The day of the month as a decimal number (01-31).
%e The day of month as a decimal number (1-31); single digits are preceded by a space.
%F The date in the format %Y-%m-%d.

Example formats for Big query data sourceFor more big query date time formats please refer the following link https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#parse_date   

Example formats for Big query data source

Date Format Expression Actual Source Date Data
%Y%m%d (or) %C%y%m%d 19701130
%y%m%d 701130
%Y/%m/%d (or) %x 1970/11/30
%Y-%m-%d (or) %F 1970-11-30
%Y %b %d (or) %Y %h %d 1970 Nov 30
%Y %B %d 1970 November 30
%m%d%Y 11301970
%m-%d-%Y 11-30-1970
%d.%m.%Y 30.11.1970
%A %b %e %Y Wednesday Nov 11 1970
%a %b %e %Y Wed Nov 11 1970

 

Snowflake date format

Format Representation
DD Day of the month (01-31).
DY First three characters of the day of the week, e.g. “Wed” for “Wednesday”.
FF Fractional seconds.
HH Hour (00-23).
MM Month (01-12).
MI Minute (00-59).
MON First 3 characters of the month (e.g. “Jan” for “January”).
SS Second (00-59).
TZH Time zone offset in hours.
TZM Time zone offset in minutes.
YYYY Year.

For more Snowflake date time formats please refer the following link https://docs.snowflake.com/en/user-guide/date-time-input-output.html#input-formats

Example formats for Snowflake data source

Date Format Expression Actual Source Date Data
YYYYMMDD 19701130
Not Available 701130 (YYMMDD)
YYYY/MM/DD 1970/11/30
YYYY-MM-DD 1970-11-30
YYYY MON DD 1970 Nov 30
YYYY MMMM DD 1970 November 30
MMDDYYYY 11301970
MM-DD-YYYY 11-30-1970
DD.MM.YYYY 30.11.1970
Not Available Wednesday Nov 11 1970
DY MON DD YYYY Wed Nov 11 1970


Redshift date format

Format Representation
DD Day of month as a number (01–31)
DY, Dy, dy Abbreviated day name (uppercase, mixed-case, lowercase, blank-padded to 3 characters)
DAY, Day, day Day name (uppercase, mixed-case, lowercase, blank-padded to 9 characters)
MM Month number (01-12)
RM, rm Month number in Roman numerals (I–XII, with I being January, uppercase or lowercase)
MONTH, Month, month Month name (uppercase, mixed-case, lowercase, blank-padded to 9 characters)
MON, Mon, mon Abbreviated month name (uppercase, mixed-case, lowercase, blank-padded to 9 characters)
YYYY, YYY, YY, Y 4-digit, 3-digit, 2-digit, 1-digit year number
IYYY, IYY, IY, I 4-digit, 3-digit, 2-digit, 1-digit International Organization for Standardization (ISO) year number
   
HH24 Hour (24-hour clock, 00–23)
HH or HH12 Hour (12-hour clock, 01–12)
MI Minutes (00–59)
SS Seconds (00–59)
MS Milliseconds (.000)
US Microseconds (.000000)
AM or PM, A.M. or P.M., a.m. or p.m., am or pm Upper and lowercase meridian indicators (for 12-hour clock)
TZ, tz Upper and lowercase time zone abbreviation; valid for TIMESTAMPTZ only

Example formats for Redshift data sourceExample formats for Redshift data sourceFor more Redshift date time formats please refer the following link https://docs.aws.amazon.com/redshift/latest/dg/r_FORMAT_strings.html

Date Format Expression Actual Source Date Data
YYYYMMDD 19701130
YYMMDD 701130 (YYMMDD)
YYYY/MM/DD 1970/11/30
YYYY-MM-DD 1970-11-30
YYYY Mon DD 1970 Nov 30
YYYY Month DD 1970 November 30
MMDDYYYY 11301970
MM-DD-YYYY 11-30-1970
DD.MM.YYYY 30.11.1970
Day Mon DD YYYY Wednesday Nov 11 1970
Dy Mon DD YYYY Wed Nov 11 1970
logo